
//-------------------------------
// TABLE A2
//-------------------------------

clear
use masterdata_policing.dta

encode state, gen(state2)
encode county, gen(county2)

gen vcrime_tot = murders + agg_assault + forc_rape + robbery
gen vcrime_clear = murders_clear + agg_assault_clear + forc_rape_clear + robbery_clear
gen vcrimeclearrate = vcrime_clear/vcrime_tot
gen pcrime_tot = burglary + larcenytheft + mvt
gen pcrime_clear = burglary_clear + larceny_clear + mvt_clear
gen pcrimeclearrate = pcrime_clear/pcrime_tot


gen youthratio = (pop15to17+pop18to24+pop25to34)/totalpopulation
gen blackratio = black/totalpopulation
gen lessthanhighratio = lessthanhigh/popover25
gen unempratio = unemployed/civilian
gen lincome = ln(medianincome)
gen lnvcrime = ln(vcrime_tot + 1)
gen lnpcrime = ln(pcrime_tot + 1)
gen lpop = ln(population)

replace vcrimeclearrate = vcrimeclearrate*100
replace pcrimeclearrate = pcrimeclearrate*100

replace policebudget = policebudget*100
replace youthratio = youthratio*100
replace blackratio = blackratio*100
replace lessthanhighratio = lessthanhighratio*100
replace unempratio = unempratio*100
replace gini = gini*100

gen polbudgetpc = totalbudget/population
gen totpolemppc =(fullswornofficer + fullcivilianemp + ptimeswornofficer + ptimecivilianemp)/population
gen swpolemppc = (fullswornofficer + ptimeswornofficer)/population
gen swfullpolemppc = fullswornofficer/population 

gen lvcrime_tot = ln(vcrime_tot +1)
gen lpcrime_tot = ln(pcrime_tot+1)
gen lvcrime_clear = ln(vcrime_clear +1)
gen lpcrime_clear = ln(pcrime_clear+1)


macro define demographic "policebudget swfullpolemppc lpop youthratio blackratio lessthanhighratio unemprati gini lincome"
macro define crimetype "murders agg_assault forc_rape robbery burglary larcenytheft mvt"

gen lncommute = ln(commutemin + 1)

by geo_fips, sort: gen lag_vcrime = lvcrime_tot[_n-1]
by geo_fips, sort: gen lag_pcrime = lpcrime_tot[_n-1]
by geo_fips, sort: gen lag_vcrimeclear = lvcrime_clear[_n-1]
by geo_fips, sort: gen lag_pcrimeclear = lpcrime_clear[_n-1]

eststo clear
eststo: quietly areg lncommute lag_vcrime lag_pcrime lag_vcrimeclear lag_pcrimeclear $demographic, a(county2) vce(cluster county2) 
eststo: quietly areg lncommute lag_vcrime lag_pcrime lag_vcrimeclear lag_pcrimeclear $demographic if population <= 28010, a(county2) vce(cluster county2) 
eststo: quietly areg lncommute lag_vcrime lag_pcrime lag_vcrimeclear lag_pcrimeclear $demographic if population > 28010, a(county2) vce(cluster county2) 
esttab, star(** 0.05 *** 0.01) ar2
esttab using crime_commuting.tex, star(** 0.05 *** 0.01) ar2 replace


